package a_o2odata_deal.utils.zhibo_zf

import org.apache.spark.{SparkConf, SparkContext}
import org.apache.spark.sql.SQLContext

/**
  * @ Auther: o2o-rd-0008
  * @ Date:   2020/1/17 15:26
  * @ Param:  ${PARAM}
  * @ Description: 
  */
object modify_zf_zhubo {
  def main(args: Array[String]): Unit = {
    val conf = new SparkConf()
    conf.setAppName(s"${this.getClass.getSimpleName}")
    conf.set("spark.debug.maxToStringFields", "500")
    //conf.setMaster("local[*]")
    conf.set("es.nodes", "192.168.1.157")
    conf.set("es.port", "9200")
    conf.set("cluster.name", "O2OElastic")
    conf.set("spark.serializer", "org.apache.spark.serializer.KryoSerializer")
    //conf.set("spark.sql.caseSensitive", "true")
    val sc = new SparkContext(conf)
    val sqlContext = new SQLContext(sc)
    sc.setLogLevel("WARN")
    sc.hadoopConfiguration.set("fs.s3a.access.key", "GAO7EO9FWKPJ8WFCQDME")
    sc.hadoopConfiguration.set("fs.s3a.secret.key", "LZ0xaHBSYKHaJ9ECDbX9f7zin79UZkXfGoNapRPL")
    //sc.hadoopConfiguration.set("fs.s3a.endpoint", "https://obs.cn-north-1.myhuaweicloud.com")

    for (months <- 1 to 1){
      sqlContext.read.orc(s"s3a://o2o-dataproces-group/panzonghao/zhibo_zf/2020/${months}/zhubo/")
        .createOrReplaceTempView("zhubo_data")
      sqlContext.read.orc(s"s3a://o2o-dataproces-group/panzonghao/zhibo_zf/2020/${months}/zhibojigou")
        .selectExpr("affiliateId","affiliate","company_name")
        .createOrReplaceTempView("jigou_data")
      sqlContext.read
        .json(a_aa_amainpackage.a_o2odata_deal.config.config.ali_zb_path)
          .selectExpr("anchorId","affiliateId")
        .createOrReplaceTempView("ali_zb")


      val tb_data = sqlContext.read.orc(s"s3a://o2o-dataproces-group/panzonghao/taobao/2020/${months}/zhibo_finally")
        .selectExpr("userId","platformId","name").dropDuplicates()

      val tm_data = sqlContext.read.orc(s"s3a://o2o-dataproces-group/panzonghao/tmall/2020/${months}/zhibo_finally")
        .selectExpr("userId","platformId","name").dropDuplicates()

      tb_data.union(tm_data).createOrReplaceTempView("all")

        sqlContext.sql(
          s"""
             |select
             |*
             |from
             |(select
             |*,
             |row_number() over(partition by userId order by cast(platformId as long) desc) as rank
             |from all)
             |where rank=1
           """.stripMargin).drop("rank")
        .createOrReplaceTempView("all_data")


      //取出达人主播的机构与公司
      sqlContext.sql(
        s"""
           |select
           |t1.anchorId,
           |t2.affiliate,
           |t2.company_name
           |from ali_zb t1
           |inner join
           |jigou_data t2
           |on t1.affiliateId=t2.affiliateId
         """.stripMargin).dropDuplicates("anchorId").createOrReplaceTempView("daren_data")

      sqlContext.sql(
        s"""
           |select
           |t1.*,
           |case when t2.anchorId is null then '-1' else '-1' end as platformId,
           |case when t2.anchorId is null then '-1' else t2.company_name end as company_name,
           |case when t2.anchorId is null then '-1' else t2.affiliate end as institution_name
           |from zhubo_data t1
           |left join
           |daren_data t2
           |on t1.anchorId=t2.anchorId
         """.stripMargin).createOrReplaceTempView("mid1")

      sqlContext.sql(
        s"""
           |select
           |t1.*,
           |case when t2.userId is null then t1.platformId else t2.platformId end as platformIds,
           |case when t2.userId is null then t1.company_name else t2.name end as company_names,
           |case when t2.userId is null then t1.institution_name else '-1' end as institution_names
           |from
           |mid1 t1
           |left join
           |all_data t2
           |on t1.anchorId=t2.userId
         """.stripMargin).drop("platformId","company_name","institution_name")
          .withColumnRenamed("platformIds","platformId")
          .withColumnRenamed("company_names","company_name")
          .withColumnRenamed("institution_names","institution_name")
        .repartition(1).write
        .orc(s"s3a://o2o-dataproces-group/panzonghao/zhibo_zf/2020/${months}/zhubo_v1/")




    }
  }

}















